package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.global.util.StringUtils;
import com.bringway.p2p.dao.P2pHplanDao;

import net.sf.json.JSONObject;
/**  
 * @类名 p2pHplanDaoImpl  
 * @功能 TODO  H计划dao层实现类
 * @作者 js 
 * @日期 2017年4月18日 上午10:55:54  
 *    
 */
@Repository
public class P2pHplanDaoImpl extends P2PJdbcDao implements P2pHplanDao {
	
	private Logger logger=Logger.getLogger(P2pHplanDaoImpl.class);

	@Override
	public List<Map<String, Object>> listXmSq(JSONObject params) {
		Integer start=params.optInt("start");
		Integer limit=params.optInt("limit");
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String xmmc=params.optString("xmmc");
		String zt=params.optString("zt");
		String sqr=params.optString("sqr");
		StringBuffer sql=new StringBuffer();
		sql.append("select ID,H_PLAN_XM_NAME,H_PLAN_NH_SYL,H_PLAN_QX_MONTH,H_PLAN_XM_JE,case when H_PLAN_XM_ZT='2' then SH_STATUS else H_PLAN_XM_ZT end H_PLAN_XM_ZT,H_PLAN_XM_JHJS,H_PLAN_TBFW");
		sql.append(",date_format(H_PLAN_FB_DATE,'%Y-%m-%d %H:%i:%s') H_PLAN_FB_DATE,H_PLAN_XM_MJQ,SQ_USER_ID,HK_TYPE,SH_STATUS");
		sql.append(",date_format(SQ_DATE,'%Y-%m-%d %H:%i:%s') SQ_DATE,SH_USER_ID,date_format(SH_DATE,'%Y-%m-%d %H:%i:%s') SH_DATE,SH_CONTENT from t_zhfw_h_plan_xm_sq where 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(H_PLAN_FB_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(H_PLAN_FB_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and H_PLAN_XM_NAME=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(zt)&&zt.length()==2) {
			sql.append(" and SH_STATUS=:zt");
			paramMap.put("zt", zt);
		}else if (StringUtils.isNotEmpty(zt)&&zt.length()==1){
			sql.append(" and H_PLAN_XM_ZT=:zt");
			paramMap.put("zt", zt);
		}
		if (StringUtils.isNotEmpty(sqr)) {
			sql.append(" and SQ_USER_ID=:sqr");
			paramMap.put("sqr", sqr);
		}
		sql.append(" order by convert(H_PLAN_XM_ZT,SIGNED),date_format(SQ_DATE,'%Y-%m-%d %H:%i:%s') desc");
		if (limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listXmSq sql is "+sql);
		logger.info("listXmSq paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getXmSqCount(JSONObject params) {
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String xmmc=params.optString("xmmc");
		String zt=params.optString("zt");
		String sqr=params.optString("sqr");
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*) from t_zhfw_h_plan_xm_sq where 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(SQ_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(SQ_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and H_PLAN_XM_NAME=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(zt)&&zt.length()==2) {
			sql.append(" and SH_STATUS=:zt");
			paramMap.put("zt", zt);
		}else if (StringUtils.isNotEmpty(zt)&&zt.length()==1){
			sql.append(" and H_PLAN_XM_ZT=:zt");
			paramMap.put("zt", zt);
		}
		if (StringUtils.isNotEmpty(sqr)) {
			sql.append(" and SQ_USER_ID=:sqr");
			paramMap.put("sqr", sqr);
		}
		logger.info("getXmSqCount sql is "+sql);
		logger.info("getXmSqCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> listXmKu(JSONObject params) {
		Integer start=params.optInt("start");
		Integer limit=params.optInt("limit");
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String xmmc=params.optString("xmmc");
		String zt=params.optString("zt");
		String qxMonth=params.optString("qxMonth");
		String isUsed=params.optString("isUsed");
		StringBuffer sql=new StringBuffer();
		sql.append("select ID,REF_H_PLAN_SQ_ID,XM_NAME,XM_NH_SYL,XM_JE,round(XM_JE*XM_NH_SYL,2) SY,XM_QX_DAY,XM_QX_MONTH,XM_ZT,XM_ORDER,XM_TYPE,XM_XYJB");
		sql.append(",XM_JHJS,XM_TBFW,IS_USED,XM_SS_GYH,date_format(IN_KU_DATE,'%Y-%m-%d %H:%i:%s') IN_KU_DATE from t_zhfw_h_plan_ku where 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(IN_KU_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(IN_KU_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and XM_NAME=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(zt)) {
			sql.append(" and XM_ZT=:zt");
			paramMap.put("zt", zt);
		}
		if (StringUtils.isNotEmpty(qxMonth)) {
			sql.append(" and XM_QX_MONTH=:qxMonth");
			paramMap.put("qxMonth", qxMonth);
		}
		if (StringUtils.isNotEmpty(isUsed)) {
			sql.append(" and IS_USED=:isUsed");
			paramMap.put("isUsed", isUsed);
		}
		sql.append(" order by date_format(IN_KU_DATE,'%Y-%m-%d %H:%i:%s') desc");
		if (limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listXmKu sql is "+sql);
		logger.info("listXmKu paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getXmKuCount(JSONObject params) {
		String startDate=params.optString("startDate");
		String endDate=params.optString("endDate");
		String xmmc=params.optString("xmmc");
		String zt=params.optString("zt");
		String qxMonth=params.optString("qxMonth");
		String isUsed=params.optString("isUsed");
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*) from t_zhfw_h_plan_ku where 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(IN_KU_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(IN_KU_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and XM_NAME=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(zt)) {
			sql.append(" and XM_ZT=:zt");
			paramMap.put("zt", zt);
		}
		if (StringUtils.isNotEmpty(qxMonth)) {
			sql.append(" and XM_QX_MONTH=:qxMonth");
			paramMap.put("qxMonth", qxMonth);
		}
		if (StringUtils.isNotEmpty(isUsed)) {
			sql.append(" and IS_USED=:isUsed");
			paramMap.put("isUsed", isUsed);
		}
		logger.info("getXmKuCount sql is "+sql);
		logger.info("getXmKuCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public boolean isKyXmKu(String gridId) {
		String sql="select count(*) from t_zhfw_h_plan_ku where ID=:gridId and IS_USED='00'";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("gridId", gridId);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap)>0;
	}

	@Override
	public String saveXmSq(JSONObject params) {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into t_zhfw_h_plan_xm_sq(ID,H_PLAN_XM_NAME,H_PLAN_NH_SYL,H_PLAN_QX_MONTH,H_PLAN_XM_JE,H_PLAN_XM_JD");
		sql.append(",H_PLAN_XM_ZT,H_PLAN_XM_JHJS,H_PLAN_TBFW,H_PLAN_FB_DATE,H_PLAN_XM_MJQ,SQ_USER_ID,SQ_DATE,HK_TYPE,SH_STATUS) values(:id,:xmmc");
		sql.append(",:nhsyl,:qx_month,:xmje,'0','2',:jhjs,:tbfw,:fbsj,:mjq,:sqr,now(),:hklx,'00')");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.putAll(params);
		String id=this.getUUId();
		paramMap.put("id", id);
		logger.info("saveXmSq sql is "+sql.toString());
		logger.info("saveXmSq paramMap is "+paramMap);
		int count=this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (count>0) {
			return id;
		}
		return null;
	}

	@Override
	public int saveXmAndKu(String xmId, String kuId) {
		String sql="insert into t_zhfw_h_plan_xm_ku(REF_H_PLAN_KU_ID,REF_H_PLAN_XM_SQ_ID) values(:kuId,:xmId)";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmId",xmId);
		paramMap.put("kuId",kuId);
		logger.info("saveXmAndKu sql is "+sql);
		logger.info("saveXmAndKu paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int updateKuIsUsed(String kuId,String isUsed) {
		String sql="update t_zhfw_h_plan_ku set IS_USED=:isUsed where ID=:kuId";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("isUsed",isUsed);
		paramMap.put("kuId",kuId);
		logger.info("updateKuIsUsed sql is "+sql);
		logger.info("updateKuIsUsed paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public List<Map<String, Object>> listKuByXmId(String xmId) {
		StringBuffer sql=new StringBuffer();
		sql.append("select t2.ID,t2.REF_H_PLAN_SQ_ID,t2.XM_NAME,t2.XM_NH_SYL,t2.XM_JE,round(t2.XM_JE*t2.XM_NH_SYL,2) SY");
		sql.append(",t2.XM_QX_DAY,t2.XM_QX_MONTH,t2.XM_ZT,t2.XM_ORDER,t2.XM_TYPE,t2.XM_XYJB,t2.XM_JHJS,t2.XM_TBFW,t2.IS_USED");
		sql.append(",t2.XM_SS_GYH,date_format(t2.IN_KU_DATE,'%Y-%m-%d %H:%i:%s') IN_KU_DATE from t_zhfw_h_plan_xm_ku t1");
		sql.append(" left join t_zhfw_h_plan_ku t2 on t1.REF_H_PLAN_KU_ID=t2.ID where t1.REF_H_PLAN_XM_SQ_ID=:xmId");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmId",xmId);
		logger.info("listKuByXmId sql is "+sql);
		logger.info("listKuByXmId paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getKuByXmIdCount(String xmId) {
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*) from t_zhfw_h_plan_xm_ku t1");
		sql.append(" left join t_zhfw_h_plan_ku t2 on t1.REF_H_PLAN_KU_ID=t2.ID where t1.REF_H_PLAN_XM_SQ_ID=:xmId");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmId",xmId);
		logger.info("getKuByXmIdCount sql is "+sql.toString());
		logger.info("getKuByXmIdCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public Map<String, Object> getXmSqMapById(String xmSqId) {
		StringBuffer sql=new StringBuffer();
		sql.append("select ID REF_XM_SQ_ID,H_PLAN_XM_NAME,H_PLAN_NH_SYL,H_PLAN_QX_MONTH,H_PLAN_XM_JE,H_PLAN_XM_JD");
		sql.append(",H_PLAN_XM_ZT,H_PLAN_XM_JHJS,H_PLAN_TBFW,H_PLAN_FB_DATE,H_PLAN_XM_MJQ,HK_TYPE");
		sql.append(" from t_zhfw_h_plan_xm_sq where ID=:xmSqId");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmSqId",xmSqId);
		logger.info("getXmSqMapById sql is "+sql.toString());
		logger.info("getXmSqMapById paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	@Override
	public int isGotoSh(String xmSqId) {
		String sql="select count(*) from t_zhfw_h_plan_xm_sq where ID=:xmSqId and SH_STATUS ='00'";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmSqId",xmSqId);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
	}
	@Override
	public String saveXmSh(Map<String, Object> paramMap) {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into t_zhfw_h_plan_xm(ID,H_PLAN_XM_NAME,H_PLAN_NH_SYL,H_PLAN_QX_MONTH,H_PLAN_XM_JE");
		sql.append(",H_PLAN_XM_JD,H_PLAN_XM_ZT,H_PLAN_XM_JHJS,H_PLAN_TBFW,H_PLAN_FB_DATE,H_PLAN_XM_MJQ");
		sql.append(",H_PLAN_JB_DATE,H_PLAN_JX_DATE,H_PLAN_DQ_DATE");
		sql.append(",H_PLAN_ORDER,HK_TYPE,HK_STATUS,HAD_HK_QS,REF_XM_SQ_ID) values(:id,:H_PLAN_XM_NAME,:H_PLAN_NH_SYL");
		sql.append(",:H_PLAN_QX_MONTH,:H_PLAN_XM_JE,'0','4',:H_PLAN_XM_JHJS,:H_PLAN_TBFW,:H_PLAN_FB_DATE");
		sql.append(",:H_PLAN_XM_MJQ,date_add(:H_PLAN_FB_DATE,interval :H_PLAN_XM_MJQ day),date_add(:H_PLAN_FB_DATE,interval :H_PLAN_XM_MJQ+1 day)");
		sql.append(",date_add(date_add(:H_PLAN_FB_DATE,interval :H_PLAN_XM_MJQ day),interval :H_PLAN_QX_MONTH month)");
		sql.append(",:order,:HK_TYPE,'01',0,:REF_XM_SQ_ID)");
		String id=this.getUUId();
		paramMap.put("id", id);
		paramMap.put("order", this.getOrderXm());
		logger.info("saveXmSh sql is "+sql.toString());
		logger.info("saveXmSh paramMap is "+paramMap);
		int count=this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (count>0) {
			return id;
		}
		return null;
	}

	@Override
	public int updateXmKuByXmSqId(String xmSqId, String xmId) {
		String sql="update t_zhfw_h_plan_xm_ku set REF_H_PLAN_XM_ID=:xmId,UPDATE_DATE=now() where REF_H_PLAN_XM_SQ_ID=:xmSqId";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmSqId",xmSqId);
		paramMap.put("xmId",xmId);
		logger.info("updateXmKuByXmSqId sql is "+sql.toString());
		logger.info("updateXmKuByXmSqId paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}
	/**
	 * @作者 js  
	 * @方法名 getOrderXm  
	 * @功能 TODO 获取项目中下一个排序
	 * @return
	 */
	private String getOrderXm(){
		String sql="Select ifnull(max(H_PLAN_ORDER),0)+1 H_PLAN_ORDER from t_zhfw_h_plan_xm";
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String order="1";
				while (rs.next()) {
					order=rs.getString("H_PLAN_ORDER");
				}
				return order;
			}
		});
	}

	@Override
	public int updateXmSqInfo(Map<String, Object> paramMap) {
		String sql="update t_zhfw_h_plan_xm_sq set H_PLAN_XM_ZT=:xmzt,SH_USER_ID=:shr,SH_DATE=now(),SH_CONTENT=:shbz,SH_STATUS=:shzt where ID=:xmSqId";
		logger.info("updateXmSqInfo sql is "+sql);
		logger.info("updateXmSqInfo paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}
	/**
	 * @author qjy
	 * p2pHplanDao的实现类
	 * 跟新项目申请表中的字段
	 */
	@Override
	public int updateSqInfo(Map<String, Object> paramMap) {
		String sql="update t_zhfw_h_plan_sq set XM_ZT=:hstate,XM_SH_USER_ID=:hshr,XM_SH_DATE=now(),XM_SH_CONTENT=:hshbz,XM_XYJB=:hxyjb where ID=:hid";
		logger.info("updateSqInfo sql is "+sql);
		logger.info("updateSqInfo paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}
	/**
	 * @author qjy
	 * p2pHplanDao的实现类
	 * 查询是否有这个申请
	 */
	@Override
	public int isGotoShplan(String hid) {
		String sql="select count(*) from t_zhfw_h_plan_sq where ID=:hid and XM_ZT='1'";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("hid",hid);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
	}
	/**
	 * @author qjy
	 * p2pHplanDao的实现类
	 * 获取H计划申请数据
	 */
	@Override
	public List queryHplanList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmname = params.optString("xmname");
		String jkzh = params.optString("jkzh");
		String shrid = params.optString("shrid");
		String state = params.optString("state");
		String xmType = params.optString("xmType");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("SELECT ID, XM_NAME, XM_NH_SYL, XM_JE, XM_QX_DAY, XM_QX_MONTH, XM_SQ_USERID, date_format(XM_SQ_DATE,'%Y-%m-%d') XM_SQ_DATE,"
				+ " XM_ZT, XM_SH_USER_ID,date_format(XM_SH_DATE,'%Y-%m-%d') XM_SH_DATE, XM_SH_CONTENT, XM_ORDER, XM_TYPE, XM_XYJB, XM_JHJS, XM_TBFW,"
				+ " XM_SS_GYH FROM t_zhfw_h_plan_sq");
		sql.append(" WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(XM_SQ_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(XM_SQ_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(xmname)){
			sql.append(" AND XM_NAME like "+ "'%"+xmname+"%'");
		}
		if(StringUtils.isNotEmpty(jkzh)){
			sql.append(" AND XM_SQ_USERID like "+ "'%"+jkzh+"%'");
		}
		if(StringUtils.isNotEmpty(shrid)){
			sql.append(" AND XM_SH_USER_ID like "+ "'%"+shrid+"%'");
		}
		if((StringUtils.isNotEmpty(state))){
			sql.append(" AND XM_ZT = :state ");
			paramMap.put("state", state);
		}
		if(StringUtils.isNotEmpty(xmType)){
			sql.append(" AND XM_TYPE = :xmType ");
			paramMap.put("xmType", xmType);
		}
		sql.append(" order by date_format(XM_SQ_DATE,'%Y-%m-%d %H:%i:%s') desc LIMIT :start,:limit");
		logger.info("queryHplanList sql is : " + sql.toString());
		logger.info("queryHplanList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author qjy
	 * p2pHplanDao的实现类
	 * 获取H计划申请数据总数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getHplanCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmname = params.optString("xmname");
		String jkzh = params.optString("jkzh");
		String shrid = params.optString("shrid");
		String state = params.optString("state");
		String xmType = params.optString("xmType");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(" select count(*) FROM d_hd_p2p.t_zhfw_h_plan_sq ");
        sql.append(" where 1=1 ");
        if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(XM_SQ_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(XM_SQ_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(xmname)){
			sql.append(" AND XM_NAME like "+ "'%"+xmname+"%'");
		}
		if(StringUtils.isNotEmpty(jkzh)){
			sql.append(" AND XM_SQ_USERID like "+ "'%"+jkzh+"%'");
		}
		if(StringUtils.isNotEmpty(shrid)){
			sql.append(" AND XM_SH_USER_ID like "+ "'%"+shrid+"%'");
		}
		if((StringUtils.isNotEmpty(state))){
			sql.append(" AND XM_ZT = :state ");
			paramMap.put("state", state);
		}
		
		if(StringUtils.isNotEmpty(xmType)){
			sql.append(" AND XM_TYPE = :xmType  ");
			paramMap.put("xmType", xmType);
		}
        return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	
	@Override
	public int getQxDayByQxMonth(String qxMonth) {
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<String,String>();
		sql.append(" select datediff(date_add(now(),interval :QX_MONTH month),now())  QX_DAY FROM DUAL ");
		paramMap.put("QX_MONTH", qxMonth);
		logger.info(" getQxDayByQxMonth sql is : " + sql.toString());
		logger.info(" getQxDayByQxMonth paramMap is : " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(),paramMap,Integer.class);
	}
	
	/**
	 * @author qjy
	 * 添加并保存审核草稿的数据
	 */
	@Override
	public String saveHplan(JSONObject params) {
        StringBuffer sql = new StringBuffer();
		String xmmc = params.optString("xmmc");
		String nhsyl = params.optString("nhsyl");
		String xmje = params.optString("xmje");
		String day = params.optString("day");
		String ssgyh = params.optString("ssgyh");
		String nouth = params.optString("nouth");
		String sqr = params.optString("sqr");
		String state = params.optString("state");
		String xmjs = params.optString("xmjs");
		String xmfw = params.optString("xmfw");
		String uuid = this.getUUId();
		sql.append(" INSERT INTO t_zhfw_h_plan_sq(ID, XM_NAME, XM_NH_SYL, XM_JE, XM_QX_DAY, XM_QX_MONTH, ");
		sql.append("XM_SQ_USERID, XM_SQ_DATE, XM_ZT, XM_ORDER,XM_TYPE, XM_JHJS, XM_TBFW, XM_SS_GYH)");
		sql.append(" VALUES (:ID,:XM_NAME,:XM_NH_SYL,:XM_JE,:XM_QX_DAY,:XM_QX_MONTH,:XM_SQ_USERID,");
		sql.append("now(),:XM_ZT,:XM_ORDER,:XM_TYPE,:XM_JHJS,:XM_TBFW,:XM_SS_GYH)");
		
		SqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("ID", uuid)
				.addValue("XM_NAME", xmmc)
                .addValue("XM_NH_SYL", nhsyl)
                .addValue("XM_JE", xmje)
                .addValue("XM_QX_DAY", day)
                .addValue("XM_QX_MONTH", nouth)
                .addValue("XM_SQ_USERID", sqr)
                .addValue("XM_ZT", "1")
                .addValue("XM_ORDER", this.getOrderSq())
                .addValue("XM_TYPE", state)
                .addValue("XM_JHJS", xmjs)
                .addValue("XM_TBFW", xmfw)
                .addValue("XM_SS_GYH", ssgyh);
		this.logger.info("saveHplan  sql : " + sql.toString());
		this.logger.info("saveHplan  parameters : " + parameters.toString());
		int count=this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
		if (count>0) {
			return uuid;
		}
		return null;
	}
	/**
	 * @author qjy
	 * p2pHplanDao的实现类
	 * 查询申请的计划根据id
	 */
	@Override
	public Map<String, Object> querySq(String hid) {
		StringBuffer sql=new StringBuffer();
		sql.append("select ID SQ_ID, XM_NAME, XM_NH_SYL, XM_JE, XM_QX_DAY, XM_QX_MONTH, XM_SQ_USERID,"
				+ " XM_SQ_DATE, XM_ZT, XM_SH_USER_ID, XM_SH_DATE, XM_SH_CONTENT, XM_ORDER,"
				+ " XM_TYPE,XM_XYJB, XM_JHJS, XM_TBFW, XM_SS_GYH");
		sql.append(" from t_zhfw_h_plan_sq where ID=:hid");
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("hid",hid);
		logger.info("querySq sql is "+sql.toString());
		logger.info("getXmSqMapById paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	@Override
	public String saveShplan(Map<String, Object> paramMap) {
		StringBuffer sql=new StringBuffer();
		sql.append("insert into t_zhfw_h_plan_ku(ID, REF_H_PLAN_SQ_ID, XM_NAME, XM_NH_SYL,"
				+ "XM_JE,XM_QX_DAY, XM_QX_MONTH, XM_ZT, XM_ORDER, XM_TYPE, XM_XYJB, XM_JHJS,"
				+ " XM_TBFW, XM_SQ_USERID, IS_USED, XM_SS_GYH, IN_KU_DATE)"
				+ "values(:id,:SQ_ID,:XM_NAME,:XM_NH_SYL,:XM_JE,:XM_QX_DAY,:XM_QX_MONTH,"
				+ "'2',:xmOrder, :XM_TYPE,:XM_XYJB,:XM_JHJS,:XM_TBFW,:XM_SQ_USERID,'00',"
				+ ":XM_SS_GYH,now())");
		String id=this.getUUId();
		paramMap.put("id", id);
		paramMap.put("xmOrder", this.getOrderKu());
		logger.info("saveShplan sql is "+sql.toString());
		logger.info("saveShplan paramMap is "+paramMap);
		int count=this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		if (count>0) {
			return id;
		}
		return null;
	}
	/**
	 * @作者 js  
	 * @方法名 getOrderSq  
	 * @功能 TODO 获取项目中下一个排序
	 * @return
	 */
	private String getOrderSq(){
		String sql="Select ifnull(max(XM_ORDER),0)+1 XM_ORDER from t_zhfw_h_plan_sq";
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String order="1";
				while (rs.next()) {
					order=rs.getString("XM_ORDER");
				}
				return order;
			}
		});
	}
	/**
	 * @作者 js  
	 * @方法名 getOrderKu  
	 * @功能 TODO 获取项目库中下一个排序
	 * @return
	 */
	private String getOrderKu(){
		String sql="Select ifnull(max(XM_ORDER),0)+1 XM_ORDER from t_zhfw_h_plan_ku";
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String order="1";
				while (rs.next()) {
					order=rs.getString("XM_ORDER");
				}
				return order;
			}
		});
	}

	@Override
	public int updateKuByXmSqId(String xmSqId) {
		String sql="update t_zhfw_h_plan_ku set IS_USED='00' where ID in (select REF_H_PLAN_KU_ID from t_zhfw_h_plan_xm_ku where REF_H_PLAN_XM_SQ_ID=:xmSqId)";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmSqId",xmSqId);
		logger.info("updateKuByXmSqId sql is "+sql);
		logger.info("updateKuByXmSqId paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}
	
	/**
	 * @author qjy
	 * queryHzhjh的实现类
	 * H组合计划项目查询
	 */
	@Override
	public List<Map<String, Object>> queryHzhjh(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmname = params.optString("xmname");
		String xmtype = params.optString("xmtype");
		String hktype = params.optString("hktype");
		String hkstate = params.optString("hkstate");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("select ID, H_PLAN_XM_NAME, H_PLAN_NH_SYL, H_PLAN_QX_MONTH, H_PLAN_XM_JE, H_PLAN_XM_JD, "
				+ " H_PLAN_XM_ZT, H_PLAN_XM_JHJS, H_PLAN_TBFW, date_format(H_PLAN_FB_DATE,'%Y-%m-%d ') H_PLAN_FB_DATE, "
				+ " H_PLAN_XM_MJQ, date_format(H_PLAN_JB_DATE,'%Y-%m-%d ')H_PLAN_JB_DATE, date_format(H_PLAN_JX_DATE,'%Y-%m-%d ') H_PLAN_JX_DATE,date_format(H_PLAN_DQ_DATE,'%Y-%m-%d ') H_PLAN_DQ_DATE, "
				+ " H_PLAN_ORDER, HK_TYPE, HK_STATUS, HAD_HK_QS, REF_XM_SQ_ID "
				+ " FROM t_zhfw_h_plan_xm");
		sql.append(" WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(H_PLAN_FB_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(H_PLAN_FB_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(xmname)){
			sql.append(" AND H_PLAN_XM_NAME like "+ "'%"+xmname+"%'");
		}
		if((StringUtils.isNotEmpty(hktype))){
			sql.append(" AND HK_TYPE = :hktype ");
			paramMap.put("hktype", hktype);
		}
		if((StringUtils.isNotEmpty(hkstate))){
			sql.append(" AND HK_STATUS = :hkstate ");
			paramMap.put("hkstate", hkstate);
		}
		if(StringUtils.isNotEmpty(xmtype)){
			sql.append(" AND H_PLAN_XM_ZT = :xmtype ");
			paramMap.put("xmtype", xmtype);
		}
		if(0 != limit){
			sql.append(" order by H_PLAN_FB_DATE desc LIMIT :start,:limit");
		}
		logger.info("queryHzhjh sql is : " + sql.toString());
		logger.info("queryHzhjh paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author qjy
	 * getHzhjhCount的实现类
	 * H组合计划项目查询数据总数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getHzhjhCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmname = params.optString("xmname");
		String xmtype = params.optString("xmtype");
		String hktype = params.optString("hktype");
		String hkstate = params.optString("hkstate");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(" select count(*) FROM d_hd_p2p.t_zhfw_h_plan_xm ");
        sql.append(" where 1=1 ");
        if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(H_PLAN_FB_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(H_PLAN_FB_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(xmname)){
			sql.append(" AND H_PLAN_XM_NAME like "+ "'%"+xmname+"%'");
		}
		if((StringUtils.isNotEmpty(hktype))){
			sql.append(" AND HK_TYPE = :hktype ");
			paramMap.put("hktype", hktype);
		}
		if((StringUtils.isNotEmpty(hkstate))){
			sql.append(" AND HK_STATUS = :hkstate ");
			paramMap.put("hkstate", hkstate);
		}
		if(StringUtils.isNotEmpty(xmtype)){
			sql.append(" AND H_PLAN_XM_ZT = :xmtype ");
			paramMap.put("xmtype", xmtype);
		}
        return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	
	/* 
	 * @方法名  geHzhjhSum 
	 * @功能   TODO H组合计划项目投资金额合计
	 * @param params
	 * @return  
	 */
	@Override
	public String geHzhjhSum(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmname = params.optString("xmname");
		String xmtype = params.optString("xmtype");
		String hktype = params.optString("hktype");
		String hkstate = params.optString("hkstate");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT sum(H_PLAN_XM_JE) FROM t_zhfw_h_plan_xm where 1=1 ");
		  if(StringUtils.isNotEmpty(startDate)){
				sql.append(" AND date_format(H_PLAN_FB_DATE,'%Y-%m-%d') >= :startDate ");
				paramMap.put("startDate",startDate);
			}
			if(StringUtils.isNotEmpty(endDate)){
				sql.append(" AND date_format(H_PLAN_FB_DATE,'%Y-%m-%d') <= :endDate ");
				paramMap.put("endDate",endDate);
			}
			if(StringUtils.isNotEmpty(xmname)){
				sql.append(" AND H_PLAN_XM_NAME like "+ "'%"+xmname+"%'");
			}
			if((StringUtils.isNotEmpty(hktype))){
				sql.append(" AND HK_TYPE = :hktype ");
				paramMap.put("hktype", hktype);
			}
			if((StringUtils.isNotEmpty(hkstate))){
				sql.append(" AND HK_STATUS = :hkstate ");
				paramMap.put("hkstate", hkstate);
			}
			if(StringUtils.isNotEmpty(xmtype)){
				sql.append(" AND H_PLAN_XM_ZT = :xmtype ");
				paramMap.put("xmtype", xmtype);
			}
		logger.info("geHzhjhSum sql is " + sql.toString());
		logger.info("geHzhjhSum paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, String.class);
	}
	
	
	/**
	 * @author qjy
	 * queryHzhjh的实现类
	 * H组合计划项目查询
	 */
	@Override
	public List<Map<String, Object>> queryHtzjl(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String userid = params.optString("userid");
		String htbh = params.optString("htbh");
		String hkzt = params.optString("hkzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("SELECT ID, USERID, XM_LY_TYPE, XM_LY_TNAME, REF_H_PLAN_XM_ID, TZJE,  date_format(TZSJ,'%Y-%m-%d')TZSJ,"
				+ " TZZT, HKZT, date_format(JXRQ,'%Y-%m-%d')JXRQ, TZSY, HTBH,"
				+ " date_format(LAST_FF_SYRQ,'%Y-%m-%d ')LAST_FF_SYRQ FROM t_zhfw_h_plan_tzrxx");
		sql.append(" WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(JXRQ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(JXRQ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND USERID like "+ "'%"+userid+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
		}
		if((StringUtils.isNotEmpty(hkzt))){
			sql.append(" AND HKZT = :hkzt ");
			paramMap.put("hkzt", hkzt);
		}
		if(0 != limit){
			sql.append(" order by JXRQ desc LIMIT :start,:limit");
		}
		logger.info("queryHtzjl sql is : " + sql.toString());
		logger.info("queryHtzjl paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author qjy
	 * getHzhjhCount的实现类
	 * H组合计划项目查询数据总数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getHtzjlCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String userid = params.optString("userid");
		String htbh = params.optString("htbh");
		String hkzt = params.optString("hkzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(" select count(*) FROM d_hd_p2p.t_zhfw_h_plan_tzrxx ");
        sql.append(" where 1=1 ");
        if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(JXRQ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(JXRQ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND USERID like "+ "'%"+userid+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
		}
		if((StringUtils.isNotEmpty(hkzt))){
			sql.append(" AND HKZT = :hkzt ");
			paramMap.put("hkzt", hkzt);
		}
        return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	
	/* 
	 * @方法名  geHzhjhSum 
	 * @功能   TODO H组合计划项目投资记录金额合计
	 * @param params
	 * @return  
	 */
	@Override
	public String geHtzjlSum(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String userid = params.optString("userid");
		String htbh = params.optString("htbh");
		String hkzt = params.optString("hkzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT sum(TZJE) FROM t_zhfw_h_plan_tzrxx where 1=1 ");
		  if(StringUtils.isNotEmpty(startDate)){
				sql.append(" AND date_format(JXRQ,'%Y-%m-%d') >= :startDate ");
				paramMap.put("startDate",startDate);
			}
			if(StringUtils.isNotEmpty(endDate)){
				sql.append(" AND date_format(JXRQ,'%Y-%m-%d') <= :endDate ");
				paramMap.put("endDate",endDate);
			}
			if(StringUtils.isNotEmpty(userid)){
				sql.append(" AND USERID like "+ "'%"+userid+"%'");
			}
			if(StringUtils.isNotEmpty(htbh)){
				sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
			}
			if((StringUtils.isNotEmpty(hkzt))){
				sql.append(" AND HKZT = :hkzt ");
				paramMap.put("hkzt", hkzt);
			}
		logger.info("geHtzjlSum sql is " + sql.toString());
		logger.info("geHtzjlSum paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, String.class);
	}
	/**
	 * @author qjy
	 * queryHzhjh的实现类
	 * H组合计划项目流标查询
	 */
	@Override
	public List<Map<String, Object>> queryHlbtzjl(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String userid = params.optString("userid");
		String htbh = params.optString("htbh");
		String tzzt = params.optString("tzzt");
		String hkzt = params.optString("hkzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("SELECT ID, USERID, XM_LY_TYPE, XM_LY_TNAME, REF_H_PLAN_XM_ID, TZJE, "
				+ "date_format(TZSJ,'%Y-%m-%d')TZSJ, TZZT, HKZT,date_format(JXRQ,'%Y-%m-%d') JXRQ,"
				+ " TZSY, HTBH, date_format(LAST_FF_SYRQ,'%Y-%m-%d')LAST_FF_SYRQ,"
				+ "date_format(BK_DATE,'%Y-%m-%d') BK_DATE"
				+ " FROM t_zhfw_h_plan_tzrxx_lb_bk");
		sql.append(" WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND USERID like "+ "'%"+userid+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
		}
		if((StringUtils.isNotEmpty(hkzt))){
			sql.append(" AND HKZT = :hkzt ");
			paramMap.put("hkzt", hkzt);
		}
		if((StringUtils.isNotEmpty(tzzt))){
			sql.append(" AND TZZT = :tzzt ");
			paramMap.put("tzzt", tzzt);
		}
		if(0 != limit){
			sql.append(" order by TZSJ desc LIMIT :start,:limit");
		}
		logger.info("queryHlbtzjl sql is : " + sql.toString());
		logger.info("queryHlbtzjl paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author qjy
	 * getHzhjhCount的实现类
	 * H组合计划项目流标查询数据总数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getHlbtzjlCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String userid = params.optString("userid");
		String htbh = params.optString("htbh");
		String tzzt = params.optString("tzzt");
		String hkzt = params.optString("hkzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(" select count(*) FROM t_zhfw_h_plan_tzrxx_lb_bk ");
        sql.append(" where 1=1 ");
        if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(userid)){
			sql.append(" AND USERID like "+ "'%"+userid+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
		}
		if((StringUtils.isNotEmpty(hkzt))){
			sql.append(" AND HKZT = :hkzt ");
			paramMap.put("hkzt", hkzt);
		}
		if((StringUtils.isNotEmpty(tzzt))){
			sql.append(" AND TZZT = :tzzt ");
			paramMap.put("tzzt", tzzt);
		}
        return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	

	/* 
	 * @方法名  geHlbtzjlSum 
	 * @功能   TODO H组合计划项目流标投资总金额
	 * @param params
	 * @return  
	 */
	@Override
	public String geHlbtzjlSum(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String userid = params.optString("userid");
		String htbh = params.optString("htbh");
		String tzzt = params.optString("tzzt");
		String hkzt = params.optString("hkzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT sum(TZJE) FROM t_zhfw_h_plan_tzrxx_lb_bk where 1=1 ");
		 if(StringUtils.isNotEmpty(startDate)){
				sql.append(" AND date_format(TZSJ,'%Y-%m-%d') >= :startDate ");
				paramMap.put("startDate",startDate);
			}
			if(StringUtils.isNotEmpty(endDate)){
				sql.append(" AND date_format(TZSJ,'%Y-%m-%d') <= :endDate ");
				paramMap.put("endDate",endDate);
			}
			if(StringUtils.isNotEmpty(userid)){
				sql.append(" AND USERID like "+ "'%"+userid+"%'");
			}
			if(StringUtils.isNotEmpty(htbh)){
				sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
			}
			if((StringUtils.isNotEmpty(hkzt))){
				sql.append(" AND HKZT = :hkzt ");
				paramMap.put("hkzt", hkzt);
			}
			if((StringUtils.isNotEmpty(tzzt))){
				sql.append(" AND TZZT = :tzzt ");
				paramMap.put("tzzt", tzzt);
			}
		logger.info("geHlbtzjlSum sql is " + sql.toString());
		logger.info("geHlbtzjlSum paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, String.class);
	}
	/**
	 * @author qjy
	 * queryHsyffmx的实现类
	 * H组合计划项目收益发放明细
	 */
	@Override
	public List<Map<String, Object>> queryHsyffmx(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String username = params.optString("username");
		String xmid = params.optString("xmid");
		String hkzt = params.optString("hkzt");
		String hklx = params.optString("hklx");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("SELECT ID, USER_NAME, XM_LY_TYPE, XM_LY_TNAME, REF_XM_ID, "
				+ "REF_TZRXX_ID, TZJE, date_format(TZSJ,'%Y-%m-%d')TZSJ, DB_HK_SYJE,"
				+ " date_format(DB_HK_DATE,'%Y-%m-%d')DB_HK_DATE, DB_HK_STATE, DB_HK_TYPE,"
				+ " DB_HK_SSQS FROM t_zhfw_h_plan_tzrxx_dbtz_syhk");
		sql.append(" WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USER_NAME like "+ "'%"+username+"%'");
		}
		if((StringUtils.isNotEmpty(xmid))){
			sql.append(" AND REF_XM_ID = :xmid ");
			paramMap.put("xmid", xmid);
		}
		if((StringUtils.isNotEmpty(hkzt))){
			sql.append(" AND DB_HK_STATE = :hkzt ");
			paramMap.put("hkzt", hkzt);
		}
		if((StringUtils.isNotEmpty(hklx))){
			sql.append(" AND DB_HK_TYPE = :hklx ");
			paramMap.put("hklx", hklx);
		}
		if(0 != limit){
			sql.append(" order by TZSJ desc LIMIT :start,:limit");
		}
		logger.info("queryHsyffmx sql is : " + sql.toString());
		logger.info("queryHsyffmx paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author qjy
	 * getHzhjhCount的实现类
	 * H组合计划项目收益发放明细记录总数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getHsyffmxCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String username = params.optString("username");
		String hkzt = params.optString("hkzt");
		String hklx = params.optString("hklx");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(" select count(*) FROM t_zhfw_h_plan_tzrxx_dbtz_syhk ");
        sql.append(" where 1=1 ");
    	if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USER_NAME like "+ "'%"+username+"%'");
		}
		if((StringUtils.isNotEmpty(hkzt))){
			sql.append(" AND DB_HK_STATE = :hkzt ");
			paramMap.put("hkzt", hkzt);
		}
		if((StringUtils.isNotEmpty(hklx))){
			sql.append(" AND DB_HK_TYPE = :hklx ");
			paramMap.put("hklx", hklx);
		}
        return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	/**
	 * @author qjy
	 * queryHhk的实现类
	 * H组合计划项目还款明细
	 */
	@Override
	public List<Map<String, Object>> queryHhk(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmid = params.optString("xmid");
		String xmmc = params.optString("xmmc");
		String jkrname = params.optString("jkrname");
		String hkqszt = params.optString("hkqszt");
		String xmlx = params.optString("xmlx");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append("SELECT ID, SSQS, date_format(YHLX_DATE,'%Y-%m-%d')YHLX_DATE, YHBJ, YHLX, YHK_XM_TYPE, REF_XM_ID, REF_XM_NAME,"
				+ " REF_KU_ID, REF_KU_NAME, REF_JKR_USERNAME, YHK_QS_STATE, YHK_YQ_TS, YHK_YQ_JE, "
				+ "date_format(HK_DATE,'%Y-%m-%d')HK_DATE, REF_HK_ID FROM t_zhfw_jkr_yhk");
		sql.append(" WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(HK_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(HK_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(xmmc)){
			sql.append(" AND REF_XM_NAME like "+ "'%"+xmmc+"%'");
		}
		if((StringUtils.isNotEmpty(xmid))){
			sql.append(" AND REF_XM_ID = :xmid ");
			paramMap.put("xmid", xmid);
		}
		if(StringUtils.isNotEmpty(jkrname)){
			sql.append(" AND REF_JKR_USERNAME like "+ "'%"+jkrname+"%'");
		}
		if((StringUtils.isNotEmpty(hkqszt))){
			sql.append(" AND YHK_QS_STATE = :hkqszt ");
			paramMap.put("hkqszt", hkqszt);
		}
		if((StringUtils.isNotEmpty(xmlx))){
			sql.append(" AND YHK_XM_TYPE = :xmlx ");
			paramMap.put("xmlx", xmlx);
		}
		if(0 != limit){
			sql.append(" order by HK_DATE desc LIMIT :start,:limit");
		}
		logger.info("queryHhk sql is : " + sql.toString());
		logger.info("queryHhk paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author qjy
	 * getHhkCount的实现类
	 * H组合计划项目还款明细记录总数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getHhkCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmid = params.optString("xmid");
		String xmmc = params.optString("xmmc");
		String jkrname = params.optString("jkrname");
		String hkqszt = params.optString("hkqszt");
		String xmlx = params.optString("xmlx");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(" select count(*) FROM t_zhfw_jkr_yhk ");
        sql.append(" where 1=1 ");
        if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(HK_DATE,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(HK_DATE,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(xmmc)){
			sql.append(" AND REF_XM_NAME like "+ "'%"+xmmc+"%'");
		}
		if((StringUtils.isNotEmpty(xmid))){
			sql.append(" AND REF_XM_ID = :xmid ");
			paramMap.put("xmid", xmid);
		}
		if(StringUtils.isNotEmpty(jkrname)){
			sql.append(" AND REF_JKR_USERNAME like "+ "'%"+jkrname+"%'");
		}
		if((StringUtils.isNotEmpty(hkqszt))){
			sql.append(" AND YHK_QS_STATE = :hkqszt ");
			paramMap.put("hkqszt", hkqszt);
		}
		if((StringUtils.isNotEmpty(xmlx))){
			sql.append(" AND YHK_XM_TYPE = :xmlx ");
			paramMap.put("xmlx", xmlx);
		}
        return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
}
